﻿using AutoMapper;

using ClosedXML.Excel;
using Dapper;
using DocumentFormat.OpenXml.Office2010.CustomUI;
using DocumentFormat.OpenXml.Office2016.Excel;
using MediatR;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MyMES.Domain.W.Liststructure;
using MyMES.Domain.zmc;
using MyMES.Error;
using MyMES.Infrastructure;
using MyMES.Infrastructure.Interface;
using MyMES.Writer.API.Application.Commends.Incominginformations;
using MyMES.Writer.API.Application.Commends.W.realize;
using Org.BouncyCastle.Asn1.Ocsp;
using System.Data;
using System.Text;


namespace MyMES.Writer.API.Controllers.Incominginformations
{
    [Route("api/[controller]/[Action]")]
    [ApiController]
    public class IncominginformationsController : ControllerBase
    {

        private readonly IMediator mediator;
        private readonly IBaseRepository<Incominginformation> baseRepository;
        private readonly IMapper mapper;
        public DatabaseContext db;

        public IncominginformationsController(IMediator mediator, IBaseRepository<Incominginformation> baseRepository, IMapper mapper, DatabaseContext db)
        {
            this.mediator = mediator;
            this.baseRepository = baseRepository;
            this.mapper = mapper;
            this.db = db;
        }




        /// <summary>
        /// 创建入库信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPost]
        public async Task<APIResult<Incominginformation>> CreateIncominginformations(CreateIncominginformationsCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 删除--逻辑删
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Incominginformation>> DelIncominginformations(DelIncominginformationsCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Incominginformation>> UpdataIncominginformations(UpdataIncominginformationsCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 修改到货通知单状态
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Arrivalnotice>> UpdataArrivalnoticeStatur(UpdataArrivalnoticeStaturCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

      



        /// <summary>
        /// 导入入库管理
        /// </summary>
        [HttpPost]
        public async Task<IActionResult> ImportInventories(IFormFile file)
        {
            var result = new APIResult<int>();
            IDbTransaction transaction = null;
            var connection = db.Connection;
            var errorLogs = new StringBuilder();

            try
            {
                // ==================== 1. 文件验证 ====================
                if (file == null || file.Length == 0)
                    return Ok(new APIResult<int> { Code = APIEnum.查询失败, Msg = "请上传有效文件" });

                if (!Path.GetExtension(file.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
                    return Ok(new APIResult<int> { Code = APIEnum.查询失败, Msg = "仅支持.xlsx格式" });

                using var stream = new MemoryStream();
                await file.CopyToAsync(stream);
                using var workbook = new XLWorkbook(stream);

                var worksheet = workbook.Worksheet(1);
                var rows = worksheet.RowsUsed().Skip(1);

                connection.Open();
                transaction = connection.BeginTransaction();

                // ==================== 2. 预加载关联数据 ====================
                // 修改后的关联数据加载部分
                var suppliers = await connection.QueryAsync<Supplier>(
                    "SELECT SupplierId, SupplierName FROM Supplier", transaction);

                // 处理重复供应商名称（示例：取第一条）
                var supplierDict = suppliers
                    .GroupBy(s => s.SupplierName.Trim(), StringComparer.OrdinalIgnoreCase)
                    .ToDictionary(
                        g => g.Key,
                        g => g.First().SupplierId, // 根据业务需求选择处理方式
                        StringComparer.OrdinalIgnoreCase
                    );

                var purchases = await connection.QueryAsync<StoragePurchase>(
                    "SELECT StoragePurchaseId, StoragePurchaseNo FROM StoragePurchase", transaction);

                // 处理重复采购订单编号
                var purchaseOrderDict = purchases
                    .GroupBy(p => p.StoragePurchaseNo.Trim(), StringComparer.OrdinalIgnoreCase)
                    .ToDictionary(
                        g => g.Key,
                        g => {
                            if (g.Count() > 1)
                                throw new Exception($"发现重复采购订单编号: {g.Key}");
                            return g.First().StoragePurchaseId;
                        },
                        StringComparer.OrdinalIgnoreCase
                    );
                var inventories = new List<Incominginformation>();
                var rowNumber = 1;

                foreach (var row in rows)
                {
                    rowNumber++;
                    try
                    {
                        // ==================== 3. 数据转换 ====================
                        // 从Excel获取原始数据
                        var SupplierName = row.Cell(3).GetValue<string>()?.Trim();
                        var StoragePurchaseNo = row.Cell(5).GetValue<string>()?.Trim();

                        // 验证并获取外键ID
                        if (!supplierDict.TryGetValue(SupplierName, out var SupplierId))
                            throw new Exception($"供应商'{SupplierName}'不存在");

                        if (!purchaseOrderDict.TryGetValue(StoragePurchaseNo, out var StoragePurchaseId))
                            throw new Exception($"采购订单'{StoragePurchaseNo}'不存在");

                        var inventory = new Incominginformation
                        {
                            StorageNo = row.Cell(1).GetValue<string>()?.Trim(),
                            StorageName = row.Cell(2).GetValue<string>()?.Trim(),
                            SupplierId = SupplierId,
                            StorageDate = DateTime.Now,
                            StoragePurchaseId = StoragePurchaseId,
                            IsDlete = ParseBool(row.Cell(6).GetValue<string>()) // 第6列是布尔值列
                        };

                        inventories.Add(inventory);
                    }
                    catch (Exception ex)
                    {
                        errorLogs.AppendLine($"第 {rowNumber} 行错误: {ex.Message}");
                    }
                }

                // ==================== 4. 错误处理 ====================
                if (errorLogs.Length > 0)
                {
                    transaction.Rollback();
                    return Ok(new APIResult<int>
                    {
                        Code = APIEnum.添加失败,
                        Msg = $"发现{errorLogs.ToString().Count(c => c == '\n')}个错误",
                       // data = errorLogs.ToString()
                    });
                }

                // ==================== 5. 批量插入 ====================
                const string sql = @"INSERT INTO incominginformation 
            (StorageNo, StorageName, SupplierId, StorageDate, StoragePurchaseId, IsDlete)
            VALUES 
            (@StorageNo, @StorageName, @SupplierId, @StorageDate, @StoragePurchaseId, @IsDlete)";

                var affectedRows = await connection.ExecuteAsync(sql, inventories, transaction);
                transaction.Commit();

                result.Code = APIEnum.添加成功;
                result.Msg = $"成功导入 {affectedRows} 条记录";
                result.data = affectedRows;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                result.Code = APIEnum.添加失败;
                result.Msg = $"系统错误: {ex.Message}";
                // 实际项目应记录日志
                // _logger.LogError(ex, "入库导入异常"); 
            }
            finally
            {
                connection?.Close();
            }

            return Ok(result);
        }

        // ==================== 辅助方法 ====================

        private bool ParseBool(string value)
        {
            if (string.IsNullOrWhiteSpace(value)) return false;

            var normalizedValue = value.Trim().ToLower();

            return normalizedValue switch
            {
                // 支持正例
                "true" or "1" or "是" or "yes" or "y" => true,
                // 支持反例
                "false" or "0" or "否" or "no" or "n" => false,
                // 明确错误提示
                _ => throw new FormatException($"无效的布尔值: '{value}'，允许的值：是/否、true/false、1/0")
            };
        }

        /// <summary>
        /// 添加物料入库信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// 
        [HttpPost]
        public async Task<APIResult<Materialmanagement>> CreateMaterialmanagement(CreateMaterialmanagementCommands request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 更新入库状态
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Arrivalnotice>> UpdataArrivalnoticeStaturs(UpdataStorageStaturCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 添加到货通知单
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        [HttpPost]
        public async Task<APIResult<Arrivalnotice>> AddArrivalnotice(AddArrivalnoticeCommands request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }
    }
}
